package com.alumni_circle.mapper.home;

import com.alumni_circle.entity.home.Sponsor;
import com.alumni_circle.entity.home.VO.SponsorViewVO;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author 龙小虬
 * @since 2020-07-06 22:11
 */

public interface SponsorMapper extends BaseMapper<Sponsor> {

    /**
     *
     * @param count
     * @return
     */
    @Select("SELECT sponsor.id,sponsor.unit,sponsor.picture,sponsor.create_time,sponsor.money,sponsor.spread,sponsor.content,sponsor.views,sponsor.phone,user.`name`,user.`icon`\n" +
            "            FROM user_sponsor,user,sponsor\n" +
            "            WHERE sponsor.state = '0' and user.id = user_sponsor.user_id and user_sponsor.sponsor_id = sponsor.id\n" +
            "            ORDER BY ${count} DESC")
    List<SponsorViewVO> selectListSponsor(@Param("count") String count);


    /**
     *
     * @param id
     * @return
     */
    @Select("SELECT sponsor.picture,sponsor.id,sponsor.unit,sponsor.create_time,sponsor.money,sponsor.spread,sponsor.content,sponsor.views,sponsor.phone,user.`name`,user.`icon` \n" +
            "FROM user_sponsor,user,sponsor\n" +
            "WHERE user.id = user_sponsor.user_id and user_sponsor.sponsor_id = sponsor.id and sponsor.state = '0'  and user.id = #{id} "+
            "ORDER BY create_time DESC")
    List<SponsorViewVO> selectMyListSponsor(@Param("id") String id);


    /**
     * 搜搜后的结果
     * @param search 搜索的条件
     * @return
     */
    @Select("SELECT sponsor.picture,sponsor.id,sponsor.unit,sponsor.create_time,sponsor.money,sponsor.spread,sponsor.content,sponsor.views,sponsor.phone,user.`name`,user.`icon` \n" +
            "            FROM user_sponsor,user,sponsor\n" +
            "            WHERE user.id = user_sponsor.user_id and sponsor.state = '0' and user_sponsor.sponsor_id = sponsor.id and (sponsor.unit like concat('%',#{search},'%') or sponsor.spread like concat('%',#{search},'%' ))\n" +
            "            ORDER BY create_time DESC")
    List<SponsorViewVO> searchListSponsor(@Param("search") String search);

    @Select("SELECT sponsor.picture,sponsor.id,sponsor.unit,sponsor.create_time,sponsor.money,sponsor.spread,sponsor.content,sponsor.views,sponsor.phone,user.`name`,user.`icon` \n" +
            "            FROM user_sponsor,user,sponsor\n" +
            "            WHERE user.id = user_sponsor.user_id and sponsor.state = '0' and user_sponsor.sponsor_id = sponsor.id and user.id = #{id} and (sponsor.unit like concat('%',#{search},'%') or sponsor.spread like concat('%',#{search},'%' ))\n" +
            "            ORDER BY create_time DESC")
    List<SponsorViewVO> searchMySponsor(@Param("id") String id, @Param("search") String search);
}
